Skip to main content

Introduction

This template helps developers (or an AI coding assistant like Cursor/Claude) to audit and optimise any SQL query executed inside the Farfalla code-base.
Copy the prompt, fill in the placeholders, and paste it into Cursor. The assistant will gather execution plans, index information, and produce a ✅ / ❌ style report.

Prompt Template​

You are analysing the performance of the following Eloquent/Laravel query (or raw SQL):

<QUERY_OR_CODE_BLOCK_HERE>

1. Show the current _indexes_ on every table touched by the query.  
   – Use `SHOW INDEXES FROM <table>`.
2. Show the full table definition to identify **ROWSTORE/COLUMNSTORE**, **SHARD KEY**, and **SORT KEY**.  
   – Use `SHOW CREATE TABLE <table>`.
3. Execute the query with `DB::enableQueryLog()` (or run it directly) and list all **generated SQL statements**.
4. For **each** SQL statement, run `EXPLAIN ANALYZE` (SingleStore) and collect the execution plan.
5. Produce a report that lists, for every table and every plan:
   - ✅ things already well-optimised (good access type, correct index, co-located shards, etc.)
   - ❌ issues detected (full scans, repartitioning, missing indexes, bad shard/sort key, etc.)
   - ⚠️ items that are acceptable for now but should be improved if time permits (e.g., low-impact full scans, moderate cardinality issues).
   - Concrete recommendations: `CREATE INDEX …`, `ALTER TABLE … SHARD KEY`, move to ROWSTORE/COLUMNSTORE, etc.
6. Summarise high-impact improvements in priority order.
7. At the end of the report, include a **step-by-step action list** (bullet points) that can be copied into Cursor’s todo list, one actionable task per line.

Tip: When the assistant asks for clarifications, provide the raw SQL generated by the query and any relevant table schema snippets.

Example Usage​

  1. Identify the Eloquent method you want to analyse, e.g. OrdersService::getSubscriptionsQuery().
  2. Replace <QUERY_OR_CODE_BLOCK_HERE> with the literal PHP code or the raw SQL.
  3. Run the prompt in Cursor. The assistant will automatically execute SHOW INDEXES, SHOW CREATE TABLE, and EXPLAIN ANALYZE using the project aliases (zar, zex).
You are analysing the performance of the following query:

OrdersService::getSubscriptionsQuery(null)->cursorPaginate(15);

… (rest of template as above)

Alias note: When running ad-hoc SQL in your terminal, use zar php artisan tinker --execute="SQL_HERE" (or zex for non-artisan commands) so the queries run inside the Docker container.

Additional Notes​

  • Laravel model conventions
    • Before running SHOW INDEXES or SHOW CREATE TABLE, first inspect the Eloquent models (and their relationships such as belongsTo, hasMany, etc.) to identify the actual table name (protected $table) and avoid failed queries.
  • SingleStore specifics
    • Prefer EXPLAIN ANALYZE over plain EXPLAIN to get execution times.
    • Remember that SHARD KEY mis-match causes repartitioning; co-locate joins on the same key.
    • ROWSTORE tables are best for high-churn OLTP; COLUMNSTORE for analytical scans.
    • Farfalla runs on SingleStore v8.7 – verify that any recommended syntax is compatible with this version.
    • Convention: every table starts with a tenant_id column; favour composite indexes where tenant_id is the first field.
  • Safety
    • Use LIMIT when running heavy selects.
    • If any step returns a 500 (server) error, stop the automated analysis and surface the error so that a human can decide how to proceed.

Index / Shard / Sort Key patterns (internal examples 2025 a)​

Use the following conventions—derived from recent 2025 migrations—when writing the concrete recommendations section of the report:

PatternExample (from migration)When to use
Multi-column HASH indexALTER TABLE prices ADD KEY discount_shelf_index (deleted_at, discount_percentage, priceable_type, priceable_id, ends_at) USING HASH;Filters with 3+ equality predicates where order of columns is not required for range scans.
Composite BTREE index for tenant scopingALTER TABLE payments ADD KEY (order_id, tenant_id);High-cardinality order_id lookups scoped by tenant_id.
Shard key + sort key combo```php
$table->shardKey('list_id');
$table->sortKey(['product_type', 'product_id']);

Guidelines:
1. **Always include `tenant_id`** in composite keys when queries filter by it.
2. For tables accessed mostly via equality, prefer **`USING HASH`**.
3. Define a **`shardKey()`** first, then a **`sortKey()`** for intra-shard ordering.
4. Keep index names descriptive: `<table>_<purpose>_index`.
5. If rebuilding a large table to add shard/sort keys, follow the *copy-swap* pattern: create new table → copy data → rename.

When your analysis suggests adding an index, reference this table to pick the appropriate pattern.

---
## See Also

- [SingleStore Documentation – Performance Tuning](https://docs.singlestore.com/)
- Internal guide: [database optimisation checklist](mdc:.cursor/rules/performance.mdc)
X

Graph View